--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_cli_climq AS (SELECT * FROM &erp.cli_climq WHERE 1=2);


DELETE FROM  se_cli_climq;


 INSERT INTO se_cli_climq
 (SELECT Nvl(cm.marque,'1') marque_cod,
         c.id client_cod,
         NULL camion_cod,
         Decode(cm.GROU,'000',NULL,'00'||cm.GROU) clihier_cod,
         NULL critmq1_cod,
         NULL critmq2_cod,
         TARIF tarcatc_cod,
         CODSUPPL suppl1_cod,
         TARIFREF tarcatg_cod,
         NULL typreleve_cod,
         NULL fournisseur_cod,
         rep rep_cod,
         NULL critmq4_cod,
         NULL critmq5_cod,
         NULL critmq6_cod,
         NULL critmq7_cod,
         NULL critmq8_cod,
         NULL critmq3_cod,
         NULL critmq9_cod,
         NULL critmq10_cod,
         cm.secteur sectcial_cod,
         cm.modreg modreg_cod,
         cm.MODEXP exped_cod,
         Nvl(Decode(cm.GROU,'000',NULL,'00'||cm.GROU),cm.clienc) clicond_cod,
         cm.modpai modpai_cod,
         0 enreg_id,
         'CLT_' || c.CATCPTA || '_' || c.TVA || Nvl(cm.tpf,'A') prfcompta_cod,
         c.CLISTAT clistat_cod,
         cm.depot2 depsor_cod,
         cm.clipay clipay_cod,
         'D' tvaregime_cod,
         NULL suppl2_cod,
         cm.port port_cod,
         cm.clienc clienc_cod,
         NULL clipic_cod,
         NULL depent_cod,
         'N' enreg_s,
         cm.adherent adherent_no,
         NULL appcond_dat,
         NULL fincond_dat,
         FRANCO/100 francoseuil_mt,
         0 indart_typ,
         Decode(LIVPART,'O',0,1) ajustdel_typ,
         Decode(RELIQUAT,'O',0,1) reliquat_typ,
         NULL tarttcht_typ,
         NULL exped_jour

    FROM uv_cclients c,uv_cmarque_client cm
    WHERE cm.client=c.id
      --AND cm.MARQUE='1'
) ;
--SELECT * FROM uv_cmarque_client WHERE client IN ('01030')
 UPDATE se_cli_climq tf SET enreg_id=(select enreg_id FROM &erp.cli_climq f WHERE f.marque_cod=tf.marque_cod AND f.client_cod=tf.client_cod) ;
 UPDATE se_cli_climq tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 commit;
